package com.util;

import com.bean.Product;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class SelectProduct {
    private String id = "sa";
    private String pwd = "123456";
    private String url = "jdbc:sqlserver://127.0.0.1:1433;databaseName=FarmProductSalesSystem";
    private List<Product> productList;
    public List<Product> getProductData(){
        productList = new ArrayList<>();
        try {
            //加载驱动
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        //获取连接
        Connection conn = null;
        Statement stat = null;
        ResultSet rs = null;
        try {
            conn = DriverManager.getConnection(url,id,pwd);
            String sql = "select * from Product";
            stat = conn.createStatement(); //用于执行sql语句
            rs = stat.executeQuery(sql); //返回查询后的结果
            while(rs.next()){
                int ProductID = rs.getInt("ProductID");
                int ProductCategoryID = rs.getInt("ProductCategoryID");
                int FarmerID = rs.getInt("FarmerID");
                String ProductName = rs.getString("ProductName");
                Double Price = rs.getDouble("Price");
                String ImagePath = rs.getString("ImagePath");
                String description = rs.getString("description");
                Product product = new Product(ProductID,ProductCategoryID,FarmerID,ProductName,Price,ImagePath,description);
                productList.add(product);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            System.out.println("连接数据库失败！");
        }finally{
            //关闭coon
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

            //关闭stat
            try {
                stat.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

            //关闭rs
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return productList;
    }

    public int getProductNumber(){
        List<Product> list = getProductData();
        int count = 0;
        for(Product product:list)
        {
            count++;
        }
        //System.out.println(count);
        return count;
    }

    public int getProductID(){
        List<Product> list = getProductData();
        int ID = 0;
        for(Product product:list)
        {
            ID = product.getProductID();
        }
        return ID;
    }

    public boolean exitProductID(int ProductID){
        List<Product> list = getProductData();
        int ID = 0;
        for(Product product:list)
        {
            ID = product.getProductID();
            if(ID==ProductID)
                return true;
        }
        return false;
    }
}
